﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: ETL_Source_View_ColumnConfigDAL.gen.cs
//
// 模块名称: ETLSourceViewColumnDal
//
// 作者: LPC
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-2-6    LPC    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using HIDB;
using Model;

namespace DAL
{
    /// <summary>
    /// ETL_Source_View_ColumnConfig表数据访问层
    /// </summary>
    public class EtlSourceViewColumnConfigDAL : DB
    {
        public EtlSourceViewColumnConfigDAL(LoginInfo info)
        {
            base.loginInfo = info;
        }
        /// <summary>
        /// 根据GUID字段获取ETL_Source_View_ColumnConfigInfo实体对象
        /// </summary>
        /// <param name="guid">GUID</param>
        /// <returns>ETL_Source_View_ColumnConfigInfo实体对象</returns>
        public ETL_Source_View_ColumnConfigInfo GetInfoById(string guid)
        {
            var list = new List<ETL_Source_View_ColumnConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",guid)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("", sqlparams);
                list = CreateEtlSourceViewColumnConfigInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", 0, 1, ex, "", "");
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        /// <summary>
        /// 往ETL_Source_View_ColumnConfig表内添加新记录
        /// </summary>
        /// <returns>添加记录索引值</returns>
        public int Insert(IDataParameter[] par)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("Working_db.UP_ETL_Source_View_ColumnConfig_Insert", par);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 更新ETL_Source_View_ColumnConfig表记录
        /// </summary>
        public int Update(ETL_Source_View_ColumnConfigInfo eTLSourceViewColumnConfigInfo)
        {
            int num = -1;

            //创建cmd对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@GUID",eTLSourceViewColumnConfigInfo.Guid),
            new SqlParameter("@ViewID",eTLSourceViewColumnConfigInfo.ViewID),
            new SqlParameter("@ColumnOrderID",eTLSourceViewColumnConfigInfo.ColumnOrderID),
            new SqlParameter("@ColumnName",eTLSourceViewColumnConfigInfo.ColumnName),
            new SqlParameter("@ColumnType",eTLSourceViewColumnConfigInfo.ColumnType),
            new SqlParameter("@ColumnLength",eTLSourceViewColumnConfigInfo.ColumnLength),
            new SqlParameter("@ColumnPrecision",eTLSourceViewColumnConfigInfo.ColumnPrecision),
            new SqlParameter("@ColumnScale",eTLSourceViewColumnConfigInfo.ColumnScale),
            new SqlParameter("@ColumnIsPK",eTLSourceViewColumnConfigInfo.ColumnIsPK),
            new SqlParameter("@ColumnIsNullable",eTLSourceViewColumnConfigInfo.ColumnIsNullable),
            new SqlParameter("@ColumnIsOrder",eTLSourceViewColumnConfigInfo.ColumnIsOrder),
            new SqlParameter("@CreateUser",eTLSourceViewColumnConfigInfo.CreateUser),
            new SqlParameter("@CreateTime",eTLSourceViewColumnConfigInfo.CreateTime),
            new SqlParameter("@UpdateUser",eTLSourceViewColumnConfigInfo.UpdateUser),
            new SqlParameter("@UpdateTime",eTLSourceViewColumnConfigInfo.UpdateTime),
            new SqlParameter("@DeleteUser",eTLSourceViewColumnConfigInfo.DeleteUser),
            new SqlParameter("@DeleteTime",eTLSourceViewColumnConfigInfo.DeleteTime),
            new SqlParameter("@IsDelete",eTLSourceViewColumnConfigInfo.IsDelete)
                                         };

            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("UP_Sys_RoleInfo_Insert", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", 0, 1, ex, "", "");
            }

            return num;
        }

        /// <summary>
        /// 根据GUID字段删除ETL_Source_View_ColumnConfigInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int DeleteViewCol(SqlParameter[] sqlparams)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("[Working_db].[UP_ETL_Source_View_ColumnConfig_Delete]", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("[Working_db].[UP_ETL_Source_View_ColumnConfig_Delete]", "[Working_db].[UP_ETL_Source_View_ColumnConfig_Delete]", 0, 1, ex, "", "");
            }
            return num;
        }

        /// <summary>
        /// 根据实体获取ETL_Source_View_ColumnConfigInfo实体List对象
        /// </summary>
        /// <returns>ETL_Source_View_ColumnConfigInfo实体对象</returns>
        public List<ETL_Source_View_ColumnConfigInfo> GetList(IDataParameter[] par)
        {
            var list = new List<ETL_Source_View_ColumnConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            //IDataParameter[] sqlparams = {
            //new SqlParameter("@GUID",eTLSourceViewColumnConfigInfo.Guid),
            //new SqlParameter("@ViewID",eTLSourceViewColumnConfigInfo.ViewID),
            //new SqlParameter("@ColumnOrderID",eTLSourceViewColumnConfigInfo.ColumnOrderID),
            //new SqlParameter("@ColumnName",eTLSourceViewColumnConfigInfo.ColumnName),
            //new SqlParameter("@ColumnType",eTLSourceViewColumnConfigInfo.ColumnType),
            //new SqlParameter("@ColumnLength",eTLSourceViewColumnConfigInfo.ColumnLength),
            //new SqlParameter("@ColumnPrecision",eTLSourceViewColumnConfigInfo.ColumnPrecision),
            //new SqlParameter("@ColumnScale",eTLSourceViewColumnConfigInfo.ColumnScale),
            //new SqlParameter("@ColumnIsPK",eTLSourceViewColumnConfigInfo.ColumnIsPK),
            //new SqlParameter("@ColumnIsNullable",eTLSourceViewColumnConfigInfo.ColumnIsNullable),
            //new SqlParameter("@ColumnIsOrder",eTLSourceViewColumnConfigInfo.ColumnIsOrder),
            //new SqlParameter("@CreateUser",eTLSourceViewColumnConfigInfo.CreateUser),
            //new SqlParameter("@CreateTime",eTLSourceViewColumnConfigInfo.CreateTime),
            //new SqlParameter("@UpdateUser",eTLSourceViewColumnConfigInfo.UpdateUser),
            //new SqlParameter("@UpdateTime",eTLSourceViewColumnConfigInfo.UpdateTime),
            //new SqlParameter("@DeleteUser",eTLSourceViewColumnConfigInfo.DeleteUser),
            //new SqlParameter("@DeleteTime",eTLSourceViewColumnConfigInfo.DeleteTime),
            //new SqlParameter("@IsDelete",eTLSourceViewColumnConfigInfo.IsDelete),
            //};

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("", par);
                list = CreateEtlSourceViewColumnConfigInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            if (list != null)
            {
                return list;
            }
            return null;
        }

        /// <summary>
        /// 根据实体获取ETL_Source_View_ColumnConfigInfo实体List对象
        /// </summary>
        /// <param name="par"> </param>
        /// <returns>ETL_Source_View_ColumnConfigInfo实体对象</returns>
        public int GetListCount(IDataParameter[] par)
        {
            int rowCount = 0;
            new List<ETL_Source_View_ColumnConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();

            //添加输入输出参数
            //IDataParameter[] sqlparams = {
            //new SqlParameter("@GUID",eTLSourceViewColumnConfigInfo.Guid),
            //new SqlParameter("@ViewID",eTLSourceViewColumnConfigInfo.ViewID),
            //new SqlParameter("@ColumnOrderID",eTLSourceViewColumnConfigInfo.ColumnOrderID),
            //new SqlParameter("@ColumnName",eTLSourceViewColumnConfigInfo.ColumnName),
            //new SqlParameter("@ColumnType",eTLSourceViewColumnConfigInfo.ColumnType),
            //new SqlParameter("@ColumnLength",eTLSourceViewColumnConfigInfo.ColumnLength),
            //new SqlParameter("@ColumnPrecision",eTLSourceViewColumnConfigInfo.ColumnPrecision),
            //new SqlParameter("@ColumnScale",eTLSourceViewColumnConfigInfo.ColumnScale),
            //new SqlParameter("@ColumnIsPK",eTLSourceViewColumnConfigInfo.ColumnIsPK),
            //new SqlParameter("@ColumnIsNullable",eTLSourceViewColumnConfigInfo.ColumnIsNullable),
            //new SqlParameter("@ColumnIsOrder",eTLSourceViewColumnConfigInfo.ColumnIsOrder),
            //new SqlParameter("@CreateUser",eTLSourceViewColumnConfigInfo.CreateUser),
            //new SqlParameter("@CreateTime",eTLSourceViewColumnConfigInfo.CreateTime),
            //new SqlParameter("@UpdateUser",eTLSourceViewColumnConfigInfo.UpdateUser),
            //new SqlParameter("@UpdateTime",eTLSourceViewColumnConfigInfo.UpdateTime),
            //new SqlParameter("@DeleteUser",eTLSourceViewColumnConfigInfo.DeleteUser),
            //new SqlParameter("@DeleteTime",eTLSourceViewColumnConfigInfo.DeleteTime),
            //new SqlParameter("@IsDelete",eTLSourceViewColumnConfigInfo.IsDelete),
            //};

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("", par);
                rowCount = dt.Rows.Count;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            return rowCount;
        }

        /// <summary>
        /// 生成ETL_Source_View_ColumnConfigInfo实体对象
        /// </summary>
        private List<ETL_Source_View_ColumnConfigInfo> CreateEtlSourceViewColumnConfigInfo(DataTable dt)
        {
            var list = new List<ETL_Source_View_ColumnConfigInfo>();
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var myEtlSourceViewColumnConfigInfo = new ETL_Source_View_ColumnConfigInfo
                {
                    Guid = DBConvert.GetString(dt.Rows[j], "GUID"),
                    ViewID = DBConvert.GetString(dt.Rows[j], "ViewID"),
                    ColumnOrderID = DBConvert.GetInt32(dt.Rows[j], "ColumnOrderID"),
                    ColumnName = DBConvert.GetString(dt.Rows[j], "ColumnName"),
                    ColumnType = DBConvert.GetString(dt.Rows[j], "ColumnType"),
                    ColumnLength = DBConvert.GetInt32(dt.Rows[j], "ColumnLength"),
                    ColumnPrecision = DBConvert.GetInt32(dt.Rows[j], "ColumnPrecision"),
                    ColumnScale = DBConvert.GetInt32(dt.Rows[j], "ColumnScale"),
                    ColumnIsPK = DBConvert.GetInt32(dt.Rows[j], "ColumnIsPK"),
                    ColumnIsNullable = DBConvert.GetInt32(dt.Rows[j], "ColumnIsNullable"),
                    ColumnIsOrder = DBConvert.GetInt32(dt.Rows[j], "ColumnIsOrder"),
                    CreateUser = DBConvert.GetString(dt.Rows[j], "CreateUser"),
                    CreateTime = DBConvert.GetDateTime(dt.Rows[j], "CreateTime"),
                    UpdateUser = DBConvert.GetString(dt.Rows[j], "UpdateUser"),
                    UpdateTime = DBConvert.GetDateTime(dt.Rows[j], "UpdateTime"),
                    DeleteUser = DBConvert.GetString(dt.Rows[j], "DeleteUser"),
                    DeleteTime = DBConvert.GetDateTime(dt.Rows[j], "DeleteTime"),
                    IsDelete = DBConvert.GetInt32(dt.Rows[j], "IsDelete"),
                    ColumnCNName = DBConvert.GetString(dt.Rows[j], "ColumnCNName"),
                    ColumnIsExtract = Convert.ToInt32(dt.Rows[j]["ColumnIsExtract"])
                };
                list.Add(myEtlSourceViewColumnConfigInfo);
                if (myEtlSourceViewColumnConfigInfo.ColumnName == "GUID")
                {
                    string aa = "aa";
                }
            }
            return list;
        }

        private List<ETL_Source_View_ColumnConfigInfo> CreateEtlSourceViewColumnConfigInfoByOutDt(DataTable dt)
        {
            var list = new List<ETL_Source_View_ColumnConfigInfo>();
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                var myEtlSourceViewColumnConfigInfo = new ETL_Source_View_ColumnConfigInfo();
                myEtlSourceViewColumnConfigInfo.ColumnOrderID = DBConvert.GetInt32(dt.Rows[j], "ColumnOrderID");
                myEtlSourceViewColumnConfigInfo.ColumnName = DBConvert.GetString(dt.Rows[j], "ColumnName");
                myEtlSourceViewColumnConfigInfo.ColumnType = DBConvert.GetString(dt.Rows[j], "ColumnType");
                myEtlSourceViewColumnConfigInfo.ColumnLength = DBConvert.ToInt32(dt.Rows[j]["ColumnLength"]);
                myEtlSourceViewColumnConfigInfo.ColumnPrecision = DBConvert.ToInt32(dt.Rows[j]["ColumnPrecision"]);// DBConvert.GetInt32(dt.Rows[j], "ColumnPrecision"),
                myEtlSourceViewColumnConfigInfo.ColumnScale = DBConvert.ToInt32(dt.Rows[j]["ColumnScale"]);
                myEtlSourceViewColumnConfigInfo.ColumnIsNullable = DBConvert.ToInt32(dt.Rows[j]["ColumnIsNullable"]);
                myEtlSourceViewColumnConfigInfo.ColumnIsPK = DBConvert.ToInt32(dt.Rows[j]["ColumnIsPK"]);

                list.Add(myEtlSourceViewColumnConfigInfo);
            }
            return list;
        }

        public void CreateTable(SqlParameter[] paraTable)
        {
            HIDB.dbo cmd = base.conn();
            try
            {
                cmd.StoredUpdate("[Working_db].[CreateStgTBfromDataSource]", paraTable);

            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }

        }

        public List<ETL_Source_View_ColumnConfigInfo> GetViewColumnById(SqlParameter[] par)
        {
            int rowCount = 0;
            var list = new List<ETL_Source_View_ColumnConfigInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = base.conn();



            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("[Working_db].[UP_ETL_Source_View_ColumnConfig_Select]", par);
                list = CreateEtlSourceViewColumnConfigInfo(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            return list;
        }

        public List<ETL_Source_View_ColumnConfigInfo> GetColumnInfoByOutSystem(SqlParameter[] columnPar, string connstr, string connType, string tbOwer)
        {
            var list = new List<ETL_Source_View_ColumnConfigInfo>();
            DataTable dt = new DataTable();
            HIDB.dbo cmd = base.conn();

            //执行命令返回结果
            try
            {
                if (connType == "1")
                {
                    cmd = HIDB.common.make(HIDB.DBType.SQLServer, connstr);
                    string sql = @"select a.name as ColumnName,a.column_id as ColumnOrderID ,b.name as ColumnType,case when b.name in ('nchar','nvarchar') and 
                                        a.max_length<>-1 then a.max_length/2 else a.max_length end as ColumnLength,a.precision as ColumnPrecision,a.scale as ColumnScale,a.is_nullable as ColumnIsNullable,
                                        case when exists(SELECT   1   FROM   sysindexkeys   WHERE   id   =   a.object_id   AND   colid=a.column_id) then 1 else 0 end ColumnIsPK from sys.columns a inner join sys.types b on 
                                        a.user_type_id=b.user_type_id where a.object_id=object_id(@viewName) order by a.column_id";

                    dt = cmd.GetDataTable(sql, columnPar);
                }
                else if (connType == "2")
                {
                    cmd = HIDB.common.make(HIDB.DBType.Oracle, connstr);
                    string sql = string.Format(@"select b.COLUMN_NAME as ColumnName,b.COLUMN_ID as ColumnOrderID,b.DATA_TYPE as ColumnType, case when b.DATA_TYPE in ('NCHAR2','NVARCHAR2') then b.DATA_LENGTH/2 else b.DATA_LENGTH end as ColumnLength
,nvl(b.DATA_PRECISION,0) as ColumnPrecision,nvl(b.DATA_SCALE,0) as ColumnScale
,case when b.NULLABLE='Y' then 1 else 0 end as ColumnIsNullable,case when c.constraint_type='P' then 1 else 0 end as ColumnIsPK 
from all_Tab_Cols b
left join 
(select x.table_name,x.column_name,y.constraint_type from user_cons_columns x inner join user_constraints y on x.constraint_name = y.constraint_name and y.constraint_type='P') c 
on b.column_name=c.column_name and b.table_name = c.table_name
where b.table_name='{0}' and b.OWNER='{1}' order by b.COLUMN_ID", columnPar[0].Value.ToString().Replace(tbOwer + ".", "").Replace("\"", ""), tbOwer.Replace("\"", ""));

                    dt = cmd.GetDataTable(sql);
                }
                else if (connType == "8")
                {
                    string sql = string.Format(@"select a.Column_Name as ColumnName,a.ORDINAL_POSITION as ColumnOrderID,a.udt_name as ColumnType,
case when a.CHARACTER_MAXIMUM_LENGTH<>-1 and a.udt_name in('nchar','nvarchar') then a.CHARACTER_MAXIMUM_LENGTH/2  
when a.CHARACTER_MAXIMUM_LENGTH is null then 0 else a.CHARACTER_MAXIMUM_LENGTH end as ColumnLength,
a.NUMERIC_PRECISION as ColumnPrecision,
a.NUMERIC_SCALE as ColumnScale,
case when a.IS_NULLABLE='NO' then 0 else 1 end as ColumnIsNullable,
0 as ColumnIsPK
from information_schema.columns a where a.table_schema='{1}' and a.table_name='{0}' order by a.ORDINAL_POSITION", columnPar[0].Value.ToString().Replace(tbOwer + ".", "").Replace("\"", ""), string.IsNullOrEmpty(tbOwer) ? "public" : tbOwer.Replace("\"", ""));

                    dt = DB.DBNpgsqlConn(connstr, sql);
                }
                else if (connType == "4")
                {
                    string sql = string.Format(@"select a.Column_Name as ColumnName,a.ORDINAL_POSITION as ColumnOrderID,a.DATA_TYPE as ColumnType,
case when a.CHARACTER_MAXIMUM_LENGTH in ('nchar','nvarchar') and  a.CHARACTER_MAXIMUM_LENGTH<>-1 then a.CHARACTER_MAXIMUM_LENGTH/2 
when isnull(a.CHARACTER_MAXIMUM_LENGTH) then 0 else a.CHARACTER_MAXIMUM_LENGTH end as ColumnLength,
case when isnull(a.NUMERIC_PRECISION) then 0 else a.NUMERIC_PRECISION end as ColumnPrecision,
case when isnull(a.NUMERIC_SCALE) then 0 else a.NUMERIC_SCALE end as ColumnScale,
case when a.IS_NULLABLE='NO' then 0 else 1 end as ColumnIsNullable,
0 as ColumnIsPK
from information_schema.columns a where a.table_schema='{0}' and a.table_name='{1}'  order by a.ORDINAL_POSITION", tbOwer.Replace("`", ""), columnPar[0].Value.ToString().Replace(tbOwer + ".", "").Replace("`", ""));

                    dt = DB.DBMySqlConn(connstr, sql);
                }
                list = CreateEtlSourceViewColumnConfigInfoByOutDt(dt);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            return list;
        }

        #region ETL_Source_View_ColumnConfig

        public List<ETL_Source_View_ColumnConfigInfo> GetSourceViewColumnConfigByViewID(string viewID)
        {

            List<ETL_Source_View_ColumnConfigInfo> result = new List<ETL_Source_View_ColumnConfigInfo>();
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = @"SELECT GUID,ViewID,ColumnOrderID,ColumnName,ColumnType,ColumnLength,ColumnPrecision
,ColumnScale,ColumnIsPK,ColumnIsNullable,ColumnIsOrder,CreateTime,ColumnCNName,ColumnIsExtract,ColumnIsPaging,ColumnIsDelete,ColumnIsBigDataTag,BigDataTagValue
FROM working_db.ETL_Source_View_ColumnConfig WHERE ViewID=@ViewID ORDER BY ColumnOrderID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ViewID",viewID)
                };
                DataTable dt = cmd.GetDataTable(strSql, cmdParms);
                result = dt.MapToList<ETL_Source_View_ColumnConfigInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            return result;
        }

        public int GetSourceViewColumnConfigCountBySystemID(string viewID)
        {

            int num = -1;
            try
            {
                HIDB.dbo cmd = base.conn();
                string strSql = @"SELECT COUNT(1) FROM working_db.ETL_Source_View_ColumnConfig WHERE ViewID=@ViewID";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@ViewID",viewID)
                };
                num = Convert.ToInt32(cmd.GetScalar(strSql, cmdParms));
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Select", 0, 1, ex, "", "");
            }
            return num;
        }


        /// <summary>
        /// 根据GUID字段删除ETL_Source_View_ColumnConfigInfo表信息
        /// </summary>
        /// <param name="guid">GUID</param>
        public int DeleteSourceViewColumnConfigByViewID(string columnID)
        {
            int num = -1;
            HIDB.dbo cmd = base.conn();
            try
            {
                string strSql = @"Delete  FROM [Working_db].[ETL_Source_View_ColumnConfig] WHERE GUID=@GUID;
                delete from [Working_db].[ViewColumnMapping] where [SourceColumnID]=@GUID";

                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",columnID)
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return num;
        }

        /// <summary>
        /// 往ETL_Source_View_ColumnConfig表内添加新记录
        /// </summary>
        /// <returns>添加记录索引值</returns>
        public int InsertSourceViewColumnConfig(ETL_Source_View_ColumnConfigInfo sourceViewColumnInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"INSERT INTO [Working_db].[ETL_Source_View_ColumnConfig]
(GUID,ViewID,ColumnOrderID,ColumnName,ColumnType,ColumnLength,ColumnPrecision,ColumnScale,ColumnIsPK
,ColumnIsNullable,ColumnIsOrder,CreateUser,CreateTime,ColumnCNName,ColumnIsExtract,ColumnIsBigDataTag,BigDataTagValue) 
VALUES(@GUID,@ViewID,@ColumnOrderID,@ColumnName,@ColumnType,@ColumnLength,@ColumnPrecision,@ColumnScale,@ColumnIsPK
,@ColumnIsNullable,@ColumnIsOrder,@CreateUser,@CreateTime,@ColumnCNName,@ColumnIsExtract,@ColumnIsBigDataTag,@BigDataTagValue);
if(@ColumnName='MDP_SourceType' and @ColumnIsBigDataTag=1)
begin
update [Working_db].[BigDateTagConfig] set TagValue=DBID where ViewID=@ViewID
end
";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",sourceViewColumnInfo.Guid)
                     ,new SqlParameter("@ViewID",sourceViewColumnInfo.ViewID)
                     ,new SqlParameter("@ColumnOrderID",sourceViewColumnInfo.ColumnOrderID)
                     ,new SqlParameter("@ColumnName",sourceViewColumnInfo.ColumnName)
                     ,new SqlParameter("@ColumnType",sourceViewColumnInfo.ColumnType)
                     ,new SqlParameter("@ColumnLength",sourceViewColumnInfo.ColumnLength)
                     ,new SqlParameter("@ColumnPrecision",sourceViewColumnInfo.ColumnPrecision)
                     ,new SqlParameter("@ColumnScale",sourceViewColumnInfo.ColumnScale)
                     ,new SqlParameter("@ColumnIsPK",sourceViewColumnInfo.ColumnIsPK)
                     ,new SqlParameter("@ColumnIsNullable",sourceViewColumnInfo.ColumnIsNullable)
                     ,new SqlParameter("@ColumnIsOrder",sourceViewColumnInfo.ColumnIsOrder)
                     ,new SqlParameter("@CreateUser",sourceViewColumnInfo.CreateUser)
                     ,new SqlParameter("@CreateTime",sourceViewColumnInfo.CreateTime)
                     ,new SqlParameter("@ColumnCNName",sourceViewColumnInfo.ColumnCNName)
                     ,new SqlParameter("@ColumnIsExtract",sourceViewColumnInfo.ColumnIsExtract) 
                     ,new SqlParameter("@ColumnIsBigDataTag",sourceViewColumnInfo.ColumnIsBigDataTag) 
                     ,new SqlParameter("@BigDataTagValue","") 
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        public int UpdateSourceViewColumnConfig(ETL_Source_View_ColumnConfigInfo sourceViewColumnInfo)
        {
            int num = -1;
            //创建cmd对象
            HIDB.dbo cmd = base.conn();
            //执行命令返回结果
            try
            {
                string strSql = @"Update [Working_db].[ETL_Source_View_ColumnConfig]
set ColumnOrderID=@ColumnOrderID,ColumnType=@ColumnType,ColumnLength=@ColumnLength,ColumnPrecision=@ColumnPrecision
,ColumnScale=@ColumnScale,ColumnIsPK=@ColumnIsPK,ColumnIsNullable=@ColumnIsNullable,ColumnIsOrder=@ColumnIsOrder
,ColumnCNName=@ColumnCNName,ColumnIsExtract=@ColumnIsExtract,ColumnIsBigDataTag=@ColumnIsBigDataTag
where GUID=@GUID;
if(@ColumnName='MDP_SourceType' and @ColumnIsBigDataTag=1)
begin
update [Working_db].[BigDateTagConfig] set TagValue=DBID where ViewID=@ViewID
end
";
                SqlParameter[] cmdParms = new SqlParameter[]
                {
                    new SqlParameter("@GUID",sourceViewColumnInfo.Guid) 
                     ,new SqlParameter("@ViewID",sourceViewColumnInfo.ViewID) 
                     ,new SqlParameter("@ColumnName",sourceViewColumnInfo.ColumnName) 
                     ,new SqlParameter("@ColumnOrderID",sourceViewColumnInfo.ColumnOrderID) 
                     ,new SqlParameter("@ColumnType",sourceViewColumnInfo.ColumnType)
                     ,new SqlParameter("@ColumnLength",sourceViewColumnInfo.ColumnLength)
                     ,new SqlParameter("@ColumnPrecision",sourceViewColumnInfo.ColumnPrecision)
                     ,new SqlParameter("@ColumnScale",sourceViewColumnInfo.ColumnScale)
                     ,new SqlParameter("@ColumnIsPK",sourceViewColumnInfo.ColumnIsPK)
                     ,new SqlParameter("@ColumnIsNullable",sourceViewColumnInfo.ColumnIsNullable)
                     ,new SqlParameter("@ColumnIsOrder",sourceViewColumnInfo.ColumnIsOrder) 
                     ,new SqlParameter("@ColumnCNName",sourceViewColumnInfo.ColumnCNName)
                     ,new SqlParameter("@ColumnIsExtract",sourceViewColumnInfo.ColumnIsExtract) 
                     ,new SqlParameter("@ColumnIsBigDataTag",sourceViewColumnInfo.ColumnIsBigDataTag)  
                };
                num = cmd.ExecuteSql(strSql, cmdParms);
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", "UP_Sys_ETL_Source_View_ColumnConfigInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }
        #endregion
    }
}